from generate_common import handleCustomApply
import json

from sqlalchemy import create_engine, text
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
import dotenv
from config import create_db_engine
# 创建数据库引擎
engine = create_db_engine()
dotenv.load_dotenv()

def genA27(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 A27 的 SQL 语句
    同一单据号下，当总数量不超过住院天数+plus_before_period)*times_period 时，同一天，收违规费用=(总数量-24)*单价。按最小单价。
    """


    # 获取 filterDatum 中的 plusBeforePeriod, timesPeriod字段
    plusBeforePeriod = filterDatum.get('plus_before_period', 0)
    timesPeriod = filterDatum.get('times_period', 1)
    
    # 获取 by
    by = filterDatum.get('by', 'default')
    
    """
    ============ 思路 ============
    在这个流程当中，将会用 vb 来统计 va 每一个 item_j_code 在 setl_id 下的总的数量，用于筛选是否不超过住院天数
    然后用 vb2 统计每一个 item_j_code 在 setl_id, item_day 下的总的数量，用于筛选是否同一天数量大于 24
    
    1. 统计 va 的 关联键 下的总数量 sum_q，插入 vb
    2. vb 关联 m 表，删除 sum_q 下不超过 m 表中同一 setl_id 的 period 的
    3. va 删除 关联键 不在 vb 当中的
    4. 统计 va 的第二关联键 下的总数量 sum_q，插入 vb2，仅保留同一天数量大于 24 的部分（即违规部分）
    5. va 删除不在 vb2 表中的 第二关联键
    6. va 计算 accu_q，按 第二关联键 分类，p 降序排列，计算 accu_q
    7. va 计算 vlt_q，accu_q - 24 的部分就是违规数量，但是不能大于 q，也不能小于 0

    参数
    ● plus_before_period：乘之前的校正
    ● times_period：乘校正
    ● by：default，sum
        ○ default：关联键是 setl_id, item_j_code，第二关联键是 setl_id, item_day, item_j_code
        ○ sum：关键是 setl_id，第二关联键是 setl_id, item_day
    """
    
    # 根据 by 决定关联键
    if by == 'default':
        joinKeys = ['setl_id','item_j_code']
        joinKeys2 = ['setl_id','item_day','item_j_code']
    elif by == 'sum':
        joinKeys = ['setl_id']
        joinKeys2 = ['setl_id','item_day']
    else:
        raise ValueError(f"A27, Unsupported by: {by}")

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        # step1
        f"""
            -- A27，step1，统计 va 的 关联键 下的总数量 sum_q，插入 vb
            INSERT INTO vlt_tmp_b vb ({', '.join(joinKeys)}, sum_q)
            SELECT {' ,'.join(joinKeys)}, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY {', '.join(joinKeys)}
        """,
        # step2
        f"""
            -- A27，step2，vb 关联 m 表，删除 sum_q 下不超过 m 表中同一 setl_id 的 (period + plus_before_period)*times_period 的
            DELETE FROM vlt_tmp_b vb
            WHERE EXISTS (
                SELECT 1 FROM m
                WHERE vb.setl_id = m.setl_id
                AND vb.sum_q <= (m.period + {plusBeforePeriod}) * {timesPeriod}
            )
        """,
        # step3
        f"""
            -- A27，step3，va 删除 关联键 不在 vb 当中的
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE {' AND '.join([f'va.{key} = vb.{key}' for key in joinKeys])}
            )
        """,
        # step4
        f"""
            -- A27，step4，统计 va 的第二关联键 下的总数量 sum_q，插入 vb2，仅保留同一天数量大于 24 的部分（即违规部分）
            INSERT INTO vlt_tmp_b2 vb2 ({', '.join(joinKeys2)}, sum_q)
            SELECT {' ,'.join(joinKeys2)}, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY {', '.join(joinKeys2)}
            HAVING SUM(va.q) > 24
        """,
        # step5
        f"""
            -- A27，step5，va 删除不在 vb2 表中的 第二关联键
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b2 vb2
                WHERE {' AND '.join([f'va.{key} = vb2.{key}' for key in joinKeys2])}
            )
        """,
        # step6
        f"""
            -- A27，step6，va 计算 accu_q，按 第二关联键 分类，p 降序排列，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY {', '.join(joinKeys2)}
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step7
        f"""
            -- A27，step7，va 计算 vlt_q，accu_q - 24 的部分就是违规数量，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - 24 < 0 THEN 0
                WHEN va.accu_q - 24 > va.q THEN va.q
                ELSE va.accu_q - 24
            END
        """,
    ]

    return sqlList

def genA49(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 A49 的 SQL 语句
    同一单据号下，time_span(time_unit)时间范围内数量>value 的
    """

    # 获取 filterDatum 中的 timeSpan 和 timeUnit 字段
    timeSpan = filterDatum.get('time_span', 0)
    timeUnit = filterDatum.get('time_unit', 'day')

    # 获取 filterDatum 中的 value 字段
    value = filterDatum['value']

    """
    ============ 思路 ============
    1. va 表计算 min10，就是分钟整除 10 获得的整数
    2. 根据 va 表汇总 item_j_code, setl_id, item_day, item_hour, min10，计算 sum_q，放在 vb 表当中，删除不违规的部分
    3. va 删除 item_j_code, setl_id, item_day, item_hour, min10 不在 vb 表中的数据
    4. va 计算 accu_q，按item_j_code, setl_id, item_day, item_hour, min10 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
    5. va 计算 vlt_q，accu_q - 2 的部分，但是不大于 q，也不小于 0

    ============ 参数 ============
    ● time_span：时间范围（其实没用）
    ● time_unit：时间单位（其实没用）
    ● value：数量阈值
    """
    
    sqlList = [
        # step1
        f"""
            -- A49，step1，va 表计算 min10，就是分钟整除 10 获得的整数
            UPDATE vlt_tmp_a va
            SET va.min10 = FLOOR(TO_NUMBER(TO_CHAR(va.item_time, 'MI')) / 10)
        """,
        # step2
        f"""
            -- A49，step2，根据 va 表汇总 item_j_code, setl_id, item_day, item_hour, min10，计算 sum_q，放在 vb 表当中，删除不违规的部分
            INSERT INTO vlt_tmp_b vb (setl_id, item_j_code, item_day, item_hour, min10, sum_q)
            SELECT va.setl_id, va.item_j_code, va.item_day, va.item_hour, va.min10, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_j_code, va.item_day, va.item_hour, va.min10
            HAVING SUM(va.q) > {value}
        """,
        # step3
        f"""
            -- A49，step3，va 删除 item_j_code, setl_id, item_day, item_hour, min10 不在 vb 表中的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_j_code = vb.item_j_code
                AND va.item_day = vb.item_day
                AND va.item_hour = vb.item_hour
                AND va.min10 = vb.min10
            )
        """,
        # step4
        f"""
            -- A49，step4，va 计算 accu_q，按item_j_code, setl_id, item_day, item_hour, min10 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY setl_id, item_j_code, item_day, item_hour, min10
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step5
        f"""
            -- A49，step5，va 计算 vlt_q，accu_q - value 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - {value} < 0 THEN 0
                WHEN va.accu_q - {value} > va.q THEN va.q
                ELSE va.accu_q - {value}
            END
        """,
    ]

    return sqlList

def genA513(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 A513 的 SQL 语句
    针对门诊开展血透的规则，需要统计一个自然周当中的费用
    """

    """
    ============ 思路 ============
    1. va 表计算 w_day，就是一个自然周的开始那一天
    2. va 汇总 psn_no，item_j_code，w_day，计算 sum_q，放在 vb 表当中，删除不违规的部分
    3. va 删除 psn_no，item_j_code，w_day 不在 vb 表中的数据
    4. va 计算 accu_q，按 psn_no，item_j_code，w_day 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
    5. va 计算 vlt_q，accu_q - 1 的部分，但是不大于 q，也不小于 0
    6. va 更新 related，当前自然周为 {date1} ~ {date1 + 6}
    """

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- A513，step1，va 表计算 w_day，就是一个自然周的开始那一天
            UPDATE vlt_tmp_a va
            SET va.w_day = TRUNC(va.item_time, 'IW')
        """,
        # step2
        f"""
            -- A513，step2，va 汇总 psn_no，item_j_code，w_day，计算 sum_q，放在 vb 表当中，删除不违规的部分
            INSERT INTO vlt_tmp_b vb (setl_id, item_j_code, j_isin, w_day, sum_q)
            SELECT va.setl_id, va.item_j_code, va.j_isin, va.w_day, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_j_code, va.j_isin, va.w_day
            HAVING SUM(va.q) > 1
        """,
        # step3
        f"""
            -- A513，step3，va 删除 psn_no，item_j_code，w_day 不在 vb 表中的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_j_code = vb.item_j_code
                AND va.j_isin = vb.j_isin
                AND va.w_day = vb.w_day
            )
        """,
        # step4
        f"""
            -- A513，step4，va 计算 accu_q，按 psn_no，item_j_code，w_day 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY setl_id, item_j_code, j_isin, w_day
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step5
        f"""
            -- A513，step5，va 计算 vlt_q，accu_q - 1 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - 1 < 0 THEN 0
                WHEN va.accu_q - 1 > va.q THEN va.q
                ELSE va.accu_q - 1
            END
        """,
        # step6
        f"""
            -- A513，step6，va 更新 related，自然周为 w_day ~ w_day + 6
            UPDATE vlt_tmp_a va
            SET va.related = '统计范围： ' || TO_CHAR(va.w_day, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(va.w_day + 6, 'YYYY-MM-DD')
            WHERE va.w_day IS NOT NULL
        """,
    ]

    return sqlList

def genA542(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    同一单据号、同一天内，用 B 的数量总和 > 用 A 的数量（各自项目的）最大值的部分就是违规。
    同一住院单据号下，同一天，收B（trigger）违规费用=（B各项之和-A（value_a_apply）任意一项最大数量）*B单价。

    1. 根据 va 表的数据，计算各 setl_id 一天内数量总和，插入 vb 表
    2. 根据 va 表的 setl_id，从 d 表中表计算各 setl_id 一天内 value_a_apply 的各项项目的数量总和，插入 vb2 表。同时处理关联数据。
    3. vb2 表根据 setl_id, item_day 作为分类，按照 q2 降序排列，给 row_number，放在 num1
    4. va 表删除 vb2 当中没有 setl_id, item_day 的数据
    5. va 表根据 setl_id, item_day 关联 vb2 表的关联数据，仅获取 vb2 的 num1=1 的数据
    6. va 表删除 q<=q2 的数据
    """
    
    # 获得filterDatum中的value_a_apply, value，并且转换为sql文字
    valueObjApply = filterDatum['value_a_apply']
    valueNames = []
    valueCodesSql, valueNamesSql = handleCustomApply(valueObjApply, valueNames, type='item', tblAbbr='d')
    
    # 当没有 valueCodesSql 的时候，说明没有任何项目符合条件，直接删除 va 表中所有数据
    if not valueCodesSql:
        return [
            f"""
                -- A542，直接删除 va 表中所有数据，因为没有任何 value_a_apply ，也就是没有任何输血项目符合条件，不在当前规则处理
                DELETE FROM vlt_tmp_a;
            """
        ]

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        # step1
        f"""
            -- A542，step1，根据 va 表的数据，计算各 setl_id 一天内数量总和，插入 vb 表
            INSERT INTO vlt_tmp_b vb (setl_id, item_day, sum_q)
            SELECT va.setl_id, va.item_day, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_day
        """,
        # step2
        f"""
            -- A542，step2，根据 va 表的 setl_id，从 d 表中表计算各 setl_id 一天内 value_a_apply 的各项项目的数量总和，插入 vb2 表。同时处理关联数据。
            INSERT INTO vlt_tmp_b2 vb2 (
                setl_id, item_day, 
                item_j_code2, item_j_name2, item_code2, item_name2, item_med_code2,
                item_hsp_code2, item_hsp_name2, item_time2, item_dept2, apply_dept2, p2, q2, c2, b2
            )
            SELECT 
                d.setl_id, d.item_day, 
                LISTAGG(distinct d.item_j_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_code2,
                LISTAGG(distinct d.item_j_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_name2,
                LISTAGG(distinct d.item_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_code2,
                LISTAGG(distinct d.item_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_name2,
                LISTAGG(distinct d.item_med_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_med_code2,
                LISTAGG(distinct d.item_hsp_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_code2,
                LISTAGG(distinct d.item_hsp_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_name2,
                TO_CHAR(MIN(d.item_time), 'yy-mm-dd hh24:mi:ss') || ' ~ ' || TO_CHAR(MAX(d.item_time), 'yy-mm-dd hh24:mi:ss') AS item_time2,
                LISTAGG(distinct d.item_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_dept2,
                LISTAGG(distinct d.apply_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS apply_dept2,
                LISTAGG(distinct round(d.p,2), ',') WITHIN GROUP (ORDER BY d.item_j_code) AS p2,
                sum(d.q) as q2, sum(d.c) as c2, sum(d.b) as b2
            FROM d
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_a va WHERE d.setl_id = va.setl_id and d.item_day = va.item_day
            ) {valueCodesSql} {valueNamesSql}
            GROUP BY d.setl_id, d.item_day
            HAVING sum(d.q) > 0
        """,
        # step3
        f"""
            -- A542，step3，vb2 表根据 setl_id, item_day 作为分类，按照 q2 降序排列，给 row_number，放在 num1
            MERGE INTO vlt_tmp_b2 vb2
            USING (
                SELECT vb2.setl_id, vb2.item_day,
                    ROW_NUMBER() OVER (PARTITION BY vb2.setl_id, vb2.item_day ORDER BY vb2.q2 DESC) AS rn
                FROM vlt_tmp_b2 vb2
            ) ranked
            ON (vb2.setl_id = ranked.setl_id AND vb2.item_day = ranked.item_day)
            WHEN MATCHED THEN
                UPDATE SET vb2.num1 = ranked.rn
        """,
        # step4
        f"""
            -- A542，step4，va 表删除 vb2 当中没有 setl_id, item_day 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b2 vb2
                WHERE va.setl_id = vb2.setl_id
                AND va.item_day = vb2.item_day
            )
        """,
        # step5
        f"""
            -- A542，step5，va 表根据 setl_id, item_day 关联 vb2 表的关联数据，仅获取 vb2 的 num1=1 的数据
            MERGE INTO vlt_tmp_a va
            USING (
                SELECT vb2.setl_id, vb2.item_day,
                    vb2.item_j_code2, vb2.item_j_name2, vb2.item_code2, vb2.item_name2, vb2.item_med_code2,
                    vb2.item_hsp_code2, vb2.item_hsp_name2, vb2.item_time2, vb2.item_dept2, vb2.apply_dept2, vb2.p2, vb2.q2, vb2.c2, vb2.b2
                FROM vlt_tmp_b2 vb2
                WHERE vb2.num1 = 1
            ) vb2
            ON (va.setl_id = vb2.setl_id AND va.item_day = vb2.item_day)
            WHEN MATCHED THEN
                UPDATE SET 
                    va.item_j_code2 = vb2.item_j_code2,
                    va.item_j_name2 = vb2.item_j_name2,
                    va.item_code2 = vb2.item_code2,
                    va.item_name2 = vb2.item_name2,
                    va.item_med_code2 = vb2.item_med_code2,
                    va.item_hsp_code2 = vb2.item_hsp_code2,
                    va.item_hsp_name2 = vb2.item_hsp_name2,
                    va.item_time2 = vb2.item_time2,
                    va.item_dept2 = vb2.item_dept2,
                    va.apply_dept2 = vb2.apply_dept2,
                    va.p2 = vb2.p2,
                    va.q2 = vb2.q2,
                    va.c2 = vb2.c2,
                    va.b2 = vb2.b2
        """,
        # step6
        f"""
            -- A542，step6，va 表删除 q<=q2 的数据
            DELETE FROM vlt_tmp_a va
            WHERE va.q <= va.q2
        """,
    ]
    return sqlList
    

def genA8(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 A8 的 SQL 语句
    针对重症监护、级别护理与其他按日收费的其他护理重复的规则
    条件：
    将当前 setl_id 的住院天数
    减去 value 当中所对应的项目，当天数量 > 12 的天数
    剩余的天数，如果trigger的sum(q) > 剩余的天数，则筛出
    """

    # 获得filterDatum中的value_apply, value，并且转换为sql文字
    valueObjApply = filterDatum['value_apply']
    valueNames = filterDatum['value']
    valueCodesSql, valueNamesSql = handleCustomApply(valueObjApply, valueNames, type='item', tblAbbr='d')
    # 如果没有重症监护、级别护理的项目，则增加一个不可能的条件
    if not valueNames and not valueCodesSql:
        valueCodesSql = "AND 1=0"
        valueNamesSql = ""

    """
    ============ 思路 ============
    在这个流程中，vb 表用于统计重症监护的天数明细，vb2 用于统计病例的重症监护天数计数，vb3 用于统计级别护理数量计数以及最终是否违规的判断

    1. 将 va 表的 setl_id，item_day，关联 d 表，统计重症监护、特级护理的数量 sum_q，放在 vb 表当中，仅保留 sum_q>12 的部分
    2. 将 vb 表按 setl_id，统计 sum_day，放入 vb2
    3. 将 va 表按 setl_id，统计 sum_q，放入 vb3
    4. vb3 表更新 vb2 的 sum_day
    5. vb3 更新 m 表的 period
    6. vb3 计算是否违规，计算标准：sum_q > period - sum_day 的就是违规。不违规的删除。
    7. vb3 更新 num1，num1 = period - sum_day
    8. va 删除不在 vb3 表中的 setl_id
    9. va 更新 num1，num1=period - sum_day；同时更新 period 与 sum_day
    10. va 计算 accu_q，按 setl_id 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
    11. va 计算 vlt_q，accu_q - num1 部分就是违规，但是不能大于 q，也不能小于 0
    12. va 更新 related，住院天数 {period}，重症监护和特级护理>12 的天数{sum_day}
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        "TRUNCATE TABLE vlt_tmp_b3",
        # step1
        f"""
            -- A8，step1，将 va 表的 setl_id，item_day，关联 d 表，统计重症监护、特级护理的数量 sum_q，放在 vb 表当中，仅保留 sum_q>12 的部分
            INSERT INTO vlt_tmp_b vb (setl_id, item_day, sum_q)
            SELECT d.setl_id, d.item_day, sum(d.q) as sum_q
            FROM d
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_a va WHERE d.setl_id = va.setl_id and d.item_day = va.item_day
            ) {valueCodesSql} {valueNamesSql}
            GROUP BY d.setl_id, d.item_day
            HAVING sum(d.q) > 12
        """,
        # step2
        f"""
            -- A8，step2，将 vb 表按 setl_id，统计 sum_day，放入 vb2
            INSERT INTO vlt_tmp_b2 vb2 (setl_id, sum_day)
            SELECT vb.setl_id, COUNT(*) AS sum_day
            FROM vlt_tmp_b vb
            GROUP BY vb.setl_id
        """,
        # step3
        f"""
            -- A8，step3，将 va 表按 setl_id，统计 sum_q，放入 vb3
            INSERT INTO vlt_tmp_b3 vb3 (setl_id, sum_q)
            SELECT va.setl_id, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id
        """,
        # step4
        f"""
            -- A8，step4，vb3 表更新 vb2 的 sum_day
            MERGE INTO vlt_tmp_b3 vb3
            USING vlt_tmp_b2 vb2
            ON (vb3.setl_id = vb2.setl_id)
            WHEN MATCHED THEN
                UPDATE SET vb3.sum_day = vb2.sum_day
        """,
        # step5
        f"""
            -- A8，step5，vb3 更新 m 表的 period
            MERGE INTO vlt_tmp_b3 vb3
            USING m
            ON (vb3.setl_id = m.setl_id)
            WHEN MATCHED THEN
                UPDATE SET vb3.period = m.period
        """,
        # step6
        f"""
            -- A8，step6，vb3 计算是否违规，计算标准：sum_q > period - sum_day 的就是违规。不违规的删除。
            DELETE FROM vlt_tmp_b3 vb3
            WHERE NOT (vb3.sum_q > NVL(vb3.period, 0) - NVL(vb3.sum_day, 0))
        """,
        # step7
        f"""
            -- A8，step7，vb3 更新 num1，num1 = period - sum_day
            UPDATE vlt_tmp_b3 vb3
            SET vb3.num1 = NVL(vb3.period, 0) - NVL(vb3.sum_day, 0)
        """,
        # step8
        f"""
            -- A8，step8，va 删除不在 vb3 表中的 setl_id
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b3 vb3
                WHERE va.setl_id = vb3.setl_id
            )
        """,
        # step9
        f"""
            -- A8，step9，va 更新 num1，num1=period - sum_day；同时更新 period 与 sum_day，使用 MERGE
            MERGE INTO vlt_tmp_a va
            USING vlt_tmp_b3 vb3
            ON (va.setl_id = vb3.setl_id)
            WHEN MATCHED THEN
            UPDATE SET 
                va.num1 = vb3.num1,
                va.period = vb3.period,
                va.sum_day = vb3.sum_day
        """,
        # step10
        f"""
            -- A8，step10，va 计算 accu_q，按 setl_id 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q，使用 MERGE
            MERGE INTO vlt_tmp_a va
            USING (
            SELECT d0_rowid,
                SUM(q) OVER (
                PARTITION BY setl_id
                ORDER BY p DESC, d0_rowid ASC
                ROWS UNBOUNDED PRECEDING
                ) AS cumulative_q
            FROM vlt_tmp_a
            ) ranked
            ON (va.d0_rowid = ranked.d0_rowid)
            WHEN MATCHED THEN
            UPDATE SET va.accu_q = ranked.cumulative_q
        """,
        # step11
        f"""
            -- A8，step11，va 计算 vlt_q，accu_q - num1 部分就是违规，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - NVL(va.num1, 0) < 0 THEN 0
                WHEN va.accu_q - NVL(va.num1, 0) > va.q THEN va.q
                ELSE va.accu_q - NVL(va.num1, 0)
            END
        """,
        # step12
        f"""
            -- A8，step12，va 更新 related，住院天数 {{}}, 重症监护和特级护理>12 的天数 {{}}
            UPDATE vlt_tmp_a va
            SET va.related = '住院天数 ' || NVL(va.period, 0) || '，重症监护和特级护理>12 的天数 ' || NVL(va.sum_day, 0)
            WHERE va.period IS NOT NULL AND va.sum_day IS NOT NULL
        """,
    ]

    return sqlList

def genB24(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 B24 的 SQL 语句
    #“血透项目”每周超3次（飞检专用），统计一个自然周中累计数量>3的、这一周的数据
    """

    """
    ============ 思路 ============
    1. va 表计算 w_day，就是一个自然周的开始那一天
    2. va 汇总 psn_no，item_j_code，w_day，计算 sum_q，放在 vb 表当中，删除不违规的部分
    3. va 删除 psn_no，item_j_code，w_day 不在 vb 表中的数据
    4. va 计算 accu_q，按 psn_no，item_j_code，w_day 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
    5. va 计算 vlt_q，accu_q - 3 的部分，但是不大于 q，也不小于 0
    6. va 更新 related，当前自然周为 {date1} ~ {date1 + 6}
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- B24，step1，va 表计算 w_day，就是一个自然周的开始那一天
            UPDATE vlt_tmp_a va
            SET va.w_day = TRUNC(va.item_time, 'IW')
        """,
        # step2
        f"""
            -- B24，step2，va 汇总 psn_no，item_j_code，w_day，计算 sum_q，放在 vb 表当中，删除不违规的部分
            INSERT INTO vlt_tmp_b vb (setl_id, item_j_code, j_isin, w_day, sum_q)
            SELECT va.setl_id, va.item_j_code, va.j_isin, va.w_day, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_j_code, va.j_isin, va.w_day
            HAVING SUM(va.q) > 3
        """,
        # step3
        f"""
            -- B24，step3，va 删除 psn_no，item_j_code，w_day 不在 vb 表中的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_j_code = vb.item_j_code
                AND va.j_isin = vb.j_isin
                AND va.w_day = vb.w_day
            )
        """,
        # step4
        f"""
            -- B24，step4，va 计算 accu_q，按 psn_no，item_j_code，w_day 分类，按 p 降序，d0_rowid 升序排序，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY setl_id, item_j_code, j_isin, w_day
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step5
        f"""
            -- B24，step5，va 计算 vlt_q，accu_q - 3 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - 3 < 0 THEN 0
                WHEN va.accu_q - 3 > va.q THEN va.q
                ELSE va.accu_q - 3
            END
        """,
        # step6
        f"""
            -- B24，step6，va 更新 related，自然周为 w_day ~ w_day + 6
            UPDATE vlt_tmp_a va
            SET va.related = '统计范围： ' || TO_CHAR(va.w_day, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(va.w_day + 6, 'YYYY-MM-DD')
            WHERE va.w_day IS NOT NULL
        """,
    ]

    return sqlList

def genE79(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 E79 的 SQL 语句
    针对麻醉中检测同时收其他监测的规则
    10 分钟内，同时收 trigger 与 itemextra
    其实就是 itemWith
    """

    # 获得 itemextra（在ruleAct 中），做sql
    itemextraObjApply = ruleAct.get('itemextra_apply', [])
    itemextraNames = ruleAct.get('itemextra', [])
    itemextraCodesSql, itemextraNamesSql = handleCustomApply(itemextraObjApply, itemextraNames, type='item', tblAbbr='d')

    # 如果 itemextraObjApply 是空的，那么就直接删除所有的 vlt_tmp_a，因为没有任何数据可以关联
    if not itemextraObjApply:
        return ["-- E79 step0, 没有任何item_extra，清空所有数据\nTRUNCATE TABLE vlt_tmp_a"]

    """
    ============ 思路 ============
    1. va 表计算 min10，就是分钟除以 10 的整数部分
    2. va 表同一个 setl_id, item_day, item_hour, min10，在 d 表中查 itemextra 的明细，汇总这些明细的item_j_code2, ... 等关联信息，插入 vb
    3. va 表删除不在 vb 当中的 setl_id, item_day, item_hour, min10 的数据
    4. va 表更新 vb 表的关联信息

    ============ 参数 ============
    ● itemextra：参与判断的
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- E79，step1，va 表计算 min10，就是分钟除以 10 的整数部分
            UPDATE vlt_tmp_a va
            SET va.min10 = FLOOR(TO_NUMBER(TO_CHAR(va.item_time, 'MI')) / 10)
        """,
        # step2
        f"""
            -- E79，step2，va 表同一个 setl_id, item_day, item_hour, min10，在 d 表中查 itemextra 的明细，汇总这些明细的item_j_code2, ... 等关联信息，插入 vb
            INSERT INTO vlt_tmp_b vb (
                setl_id, item_day, item_hour, min10, 
                item_j_code2, item_j_name2, item_code2, item_name2, item_med_code2, item_hsp_code2, item_hsp_name2,
                item_time2, item_dept2, apply_dept2, p2, q2, c2, b2
            )
            SELECT d.setl_id, d.item_day, d.item_hour, 
                TRUNC(TO_NUMBER(TO_CHAR(d.item_time, 'MI')) / 10, 0) AS min10,
                LISTAGG(DISTINCT d.item_j_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_code2,
                LISTAGG(DISTINCT d.item_j_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_name2,
                LISTAGG(DISTINCT d.item_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_code2,
                LISTAGG(DISTINCT d.item_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_name2,
                LISTAGG(DISTINCT d.item_med_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_med_code2,
                LISTAGG(DISTINCT d.item_hsp_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_code2,
                LISTAGG(DISTINCT d.item_hsp_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_name2,
                TO_CHAR(MIN(d.item_time), 'yy-mm-dd hh24:mi:ss') || ' ~ ' || TO_CHAR(MAX(d.item_time), 'yy-mm-dd hh24:mi:ss') AS item_time2,
                LISTAGG(DISTINCT d.item_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_dept2,
                LISTAGG(DISTINCT d.apply_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS apply_dept2,
                LISTAGG(DISTINCT d.p, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS p2,
                SUM(d.q) AS q2, SUM(d.c) AS c2, SUM(d.b) AS b2                 
            FROM d
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_a va
                WHERE d.setl_id = va.setl_id
                    AND d.item_day = va.item_day
                    AND d.item_hour = va.item_hour
                    AND FLOOR(TO_NUMBER(TO_CHAR(d.item_time, 'MI')) / 10) = va.min10
            ) {itemextraCodesSql} {itemextraNamesSql}
            GROUP BY d.setl_id, d.item_day, d.item_hour, TRUNC(TO_NUMBER(TO_CHAR(d.item_time, 'MI')) / 10, 0)
            HAVING SUM(d.q) > 0
        """,
        # step3
        f"""
            -- E79，step3，va 表删除不在 vb 当中的 setl_id, item_day, item_hour, min10 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_day = vb.item_day
                AND va.item_hour = vb.item_hour
                AND va.min10 = vb.min10
            )
        """,
        # step4
        f"""
            -- E79，step4，va 表更新 vb 表的关联信息
            UPDATE vlt_tmp_a va
            SET (va.item_j_code2, va.item_j_name2, va.item_code2, va.item_name2, va.item_med_code2, va.item_hsp_code2, va.item_hsp_name2,
                va.item_time2, va.item_dept2, va.apply_dept2, va.p2, va.q2, va.c2, va.b2) = (
                SELECT vb.item_j_code2, vb.item_j_name2, vb.item_code2, vb.item_name2, vb.item_med_code2, vb.item_hsp_code2, vb.item_hsp_name2,
                    vb.item_time2, vb.item_dept2, vb.apply_dept2, vb.p2, vb.q2, vb.c2, vb.b2
                FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                    AND va.item_day = vb.item_day
                    AND va.item_hour = vb.item_hour
                    AND va.min10 = vb.min10
            )
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_day = vb.item_day
                AND va.item_hour = vb.item_hour
                AND va.min10 = vb.min10
            )
        """
    ]

    return sqlList

def genI107(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 I107 的 SQL 语句
    统计出现的所有明细，根据明细对应的 j_yearold
    如果 j_yearold 的整数部分是 >= 18的，就违规
    如果 j_yearold 的整数部分是 <= 3岁的，则同一患者当年内使用天数去重计数 > 180 的部分违规（统计当年1月1日以后且使用天数及以前的部分）
    如果 j_yearold 的整数部分是 3-18 岁的，则同一患者当年内使用天数去重计数 > 90 的部分违规（统计当年1月1日以后且使用天数及以前的部分）
    """

    """
    ============ 思路 ============
    1. 通过 setl_id， 从 m 表关联 j_yearold 与 age，更新到 va 表
    2. 统计 va 表按 j_year, psn_no, item_j_code 分类，求 sum_q，插入到vb，仅保留以下三种情况至少符合其中一种的部分
        a. j_yearold >= 18 and nvl(age,20) > 18
        b. ( j_yearold>3 and j_yearold<18 and nvl(age,10)>3 and nvl(age,10)<18 ) and sum_q>90
        c. ( j_yearold<=3 and nvl(age,0)<=3 ) and sum_q >180
    3. 删除 va 表当中且不在 vb 表中的 j_year, psn_no, item_j_code 的部分
    4. 计算 va 表的 accu_q，按 j_year, psn_no, item_j_code，按 p, d0_rowid 降序，d0_rowid 升序排列，计算 accu_q
    5. 更新 va 表的 num1 作为数量阈值，按照下面三种情况
        a. WHEN j_yearold >= 18 and nvl(age,20) > 18 THEN num1=0
        b. WHEN  j_yearold>3 and j_yearold<18 and nvl(age,10)>3 and nvl(age,10)<18 THEN num1=90
        c. WHEN  j_yearold<=3 and nvl(age,0)<=3 THEN num1=180
    6. 计算 va 表的 vlt_q，阈值就是 accu_q > num1 的部分，但是不能大于 q，也不能小于 0
    """

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- I107，step1，通过 setl_id， 从 m 表关联 j_yearold 与 age，更新到 va 表
            MERGE INTO vlt_tmp_a va
            USING m
            ON (va.setl_id = m.setl_id)
            WHEN MATCHED THEN
                UPDATE SET va.j_yearold = m.j_yearold, va.age = m.age
        """,
        # step2
        f"""
            -- I107，step2，统计 va 表按 j_year, psn_no, item_j_code 分类，求 sum_q，插入到vb，仅保留以下三种情况至少符合其中一种的部分
            INSERT INTO vlt_tmp_b vb (j_year, psn_no, item_j_code, j_yearold, age, sum_q)
            SELECT va.j_year, va.psn_no, va.item_j_code, MIN(va.j_yearold) as j_yearold, MIN(va.age) as age, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.j_year, va.psn_no, va.item_j_code
            HAVING (
                (MIN(va.j_yearold) >= 18 AND NVL(MIN(va.age), 20) > 18)
                OR ((MIN(va.j_yearold) > 3 AND MIN(va.j_yearold) < 18) AND (NVL(MIN(va.age), 10) > 3 AND NVL(MIN(va.age), 10) < 18) AND SUM(va.q) > 90)
                OR ((MIN(va.j_yearold) <= 3) AND (NVL(MIN(va.age), 0) <= 3) AND SUM(va.q) > 180)
            )
        """,
        # step3
        f"""
            -- I107，step3，删除 va 表当中且不在 vb 表中的 j_year, psn_no, item_j_code 的部分
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.j_yearold = vb.j_yearold
                AND va.psn_no = vb.psn_no
                AND va.item_j_code = vb.item_j_code
            )
        """,
        # step4
        f"""
            -- I107，step4，计算 va 表的 accu_q，按 j_year, psn_no, item_j_code，按 p 降序，d0_rowid 升序排列，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY j_yearold, psn_no, item_j_code
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step5
        f"""
            -- I107，step5，更新 va 表的 num1 作为数量阈值，按照下面三种情况
            UPDATE vlt_tmp_a va
            SET va.num1 = CASE 
                WHEN va.j_yearold >= 18 AND NVL(va.age, 20) > 18 THEN 0
                WHEN va.j_yearold > 3 AND va.j_yearold < 18 AND (NVL(va.age, 10) > 3 AND NVL(va.age, 10) < 18) THEN 90
                WHEN va.j_yearold <= 3 AND NVL(va.age, 0) <= 3 THEN 180
                ELSE NULL
            END
        """,
        # step6
        f"""
            -- I107，step6，计算 va 表的 vlt_q，阈值就是 accu_q > num1 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - NVL(va.num1, 0) < 0 THEN 0
                WHEN va.accu_q - NVL(va.num1, 0) > va.q THEN va.q
                ELSE va.accu_q - NVL(va.num1, 0)
            END
        """,        
    ]

    return sqlList

def genI137(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 I137 的 SQL 语句
    判断次要手术收费需要折价的规则
    trigger 当中的手术，手术种类>1 ，各项手术数量都>1 且是整数
    """

    # 获得 trigger_apply 中的手术项目
    triggerObjApply = ruleAct.get('trigger_apply', [])
    triggerNames = ruleAct['trigger']
    triggerCodesSql, triggerNamesSql = handleCustomApply(triggerObjApply, triggerNames, type='item', tblAbbr='d')

    """
    ============ 思路 ============
    trigger 当中的手术，手术种类>1 ，各项手术数量都>1 且是整数
    1. va 按 setl_id, item_j_code, item_day, item_hour 汇总，求 sum_q、max(p)=>作为 p，插入 vb，并且仅保留 sum_q>=1 且 sum_q 是整数的
    2. vb 按 setl_id, item_day, item_hour 汇总，按 p 降序排序，获得 row_number()，作为 num1
    3. va 删除不在 vb 的 setl_id, item_j_code, item_day, item_hour 的数据
    4. va 按 setl_id, item_j_code, item_day, item_hour 计算 accu_q，按 p 降序、d0_rowid 升序排列，计算 accu_q
    5. 关联 vb 的 num1 作为 va 的 num1，按 setl_id, item_day, item_hour, item_j_code 关联
    6. 计算 va 的 vlt_q
        a. 当 num1=1 时，vlt_q=0
        b. 当 num1>1 时，vlt_q = GREATEST( LEAST( accu_q - 0.5 , q) , 0  )即 accu_q 大于 0.5 的部分，但是不超过 q，也不小于 0
    7. 计算 va 的各个 setl_id, item_day 下的总的 vlt_q 求和，如果 总的 vlt_q<=0，则删除这些 setl_id, item_day 下的所有数据   

    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- I137，step1，va 按 setl_id, item_j_code, item_day, item_hour 汇总，求 sum_q、max(p)=>作为 p，插入 vb，并且仅保留 sum_q>=1 且 sum_q 是整数的
            INSERT INTO vlt_tmp_b vb (setl_id, item_j_code, item_day, item_hour, p, sum_q)
            SELECT va.setl_id, va.item_j_code, va.item_day, va.item_hour, MAX(va.p) AS p, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_j_code, va.item_day, va.item_hour
            HAVING SUM(va.q) >= 1 AND SUM(va.q) = FLOOR(SUM(va.q))
        """,
        # step2
        f"""
            -- I137，step2，vb 按 setl_id, item_day, item_hour 汇总，按 p 降序排序，获得 row_number()，作为 num1
            MERGE INTO vlt_tmp_b vb
            USING (
                SELECT vb_inner.setl_id, vb_inner.item_day, vb_inner.item_hour, vb_inner.item_j_code,
                    ROW_NUMBER() OVER (
                        PARTITION BY vb_inner.setl_id, vb_inner.item_day, vb_inner.item_hour
                        ORDER BY vb_inner.p DESC
                    ) AS rn
                FROM vlt_tmp_b vb_inner
            ) ranked
            ON (vb.setl_id = ranked.setl_id AND vb.item_day = ranked.item_day AND vb.item_hour = ranked.item_hour AND vb.item_j_code = ranked.item_j_code)
            WHEN MATCHED THEN
                UPDATE SET vb.num1 = ranked.rn
        """,
        # step3
        f"""
            -- I137，step3，va 删除不在 vb 的 setl_id, item_j_code, item_day, item_hour 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_j_code = vb.item_j_code
                AND va.item_day = vb.item_day
                AND va.item_hour = vb.item_hour
            )
        """,
        # step4
        f"""
            -- I137，step4，va 按 setl_id, item_j_code, item_day, item_hour 计算 accu_q，按 p 降序、d0_rowid 升序排列，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY setl_id, item_j_code, item_day, item_hour
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step5
        f"""
            -- I137，step5，关联 vb 的 num1 作为 va 的 num1，按 setl_id, item_day, item_hour, item_j_code 关联
            MERGE INTO vlt_tmp_a va
            USING vlt_tmp_b vb
            ON (va.setl_id = vb.setl_id AND va.item_day = vb.item_day AND va.item_hour = vb.item_hour AND va.item_j_code = vb.item_j_code)
            WHEN MATCHED THEN
                UPDATE SET va.num1 = vb.num1
        """,
        # step6
        f"""
            -- I137，step6，计算 va 的 vlt_q
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE
                WHEN va.num1 = 1 THEN 0
                WHEN va.num1 > 1 THEN GREATEST(LEAST(va.accu_q - 0.5, va.q), 0)
                ELSE NULL
            END
        """,
        # step7
        f"""
            -- I137，step7，计算 va 的各个 setl_id, item_day 下的总的 vlt_q 求和，如果 总的 vlt_q<=0，则删除这些 setl_id, item_day 下的所有数据   
            DELETE FROM vlt_tmp_a va
            WHERE EXISTS (
                SELECT 1 FROM (
                    SELECT va_inner.setl_id, va_inner.item_day, SUM(va_inner.vlt_q) AS total_vlt_q
                    FROM vlt_tmp_a va_inner
                    GROUP BY va_inner.setl_id, va_inner.item_day
                    HAVING SUM(va_inner.vlt_q) <= 0
                ) totals
                WHERE va.setl_id = totals.setl_id
                AND va.item_day = totals.item_day
            )
        """,
    ]
    

    return sqlList

def genI49(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 I49 的 SQL 语句
    针对“每月收费 1 次”或者“每周收费 1 次”、“每周超 2 次”等需要按周期计算的规则。
    第一次收取后，每隔circle天为自然日，收大于numsLimit次
    """

    # 从filterDatum中获取 circle 和 numsLimit
    circle = filterDatum['circle']
    numsLimit = filterDatum['nums_limit']

    """
    ============ 思路 ============
    第一次收取后，每隔circle天为自然日，收大于numsLimit次

    在这个流程中，vb 用于统计最早日期以及初步筛选，vb2 用于计算周期起始日，vb3 用于统计总数量并且应用条件

    1. va 按 setl_id, item_j_code，统计最早的使用日，这个最早使用日作为 date1；同时统计 sum_q，插入 vb，仅保留数量 > numsLimit 的部分
    2. va 删除 vb 中不存在的 setl_id, item_j_code
    3. va 将 d0_rowid, setl_id, item_j_code, item_day, q 插入到 vb2
    4. vb2 更新 vb 的 date1（最早的使用日）
    5. vb2 计算周期开始日，作为 date2（周期开始日）
    6. vb2 根据 setl_id, item_j_code, date2 作为分类，统计 sum_q，插入 vb3，并且删除不违规的部分，即删除 NOT ( 数量 > numsLimit ) 的部分
    7. vb2 删除不存在于 vb3 的 setl_id, item_j_code, date2 
    8. vb2 计算 accu_q，按 setl_id, item_j_code, date2 做分类，按 p 降序、d0_rowid 做升序，计算 accu_q
    9. vb2 计算 vlt_q，accu_q - numsLimit 的部分，但是不能大于 q，也不能小于 0
    10. vb2 更新 vb3 的 sum_q
    11. vb2 更新 related，统计范围为 {date1} ~ {date1 + circle}，时间范围内总数量是 sum_q
    12. va 删除不存在于 vb2 的 d0_rowid 的数据
    13. va 根据 vb2 的 d0_rowid 更新 vb2 的 vlt_q, related

    ============ 参数 ============
    ● circle：周期
    ● nums_limit：数量阈值    
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        "TRUNCATE TABLE vlt_tmp_b3",
        # step1
        f"""
            -- I49，step1，va 按 setl_id, item_j_code，统计最早的使用日，这个最早使用日作为 date1；同时统计 sum_q，插入 vb，仅保留数量 > numsLimit 的部分
            INSERT INTO vlt_tmp_b vb (setl_id, item_j_code, date1, sum_q)
            SELECT va.setl_id, va.item_j_code, MIN(TRUNC(va.item_time)) AS date1, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.setl_id, va.item_j_code
            HAVING SUM(va.q) > {numsLimit}
        """,
        # step2
        f"""
            -- I49，step2，va 删除 vb 中不存在的 setl_id, item_j_code
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.setl_id = vb.setl_id
                AND va.item_j_code = vb.item_j_code
            )
        """,
        # step3
        f"""
            -- I49，step3，va 将 d0_rowid, setl_id, item_j_code, item_day, q 插入到 vb2
            INSERT INTO vlt_tmp_b2 vb2 (d0_rowid, setl_id, item_j_code, item_day, q)
            SELECT va.d0_rowid, va.setl_id, va.item_j_code, va.item_day, va.q
            FROM vlt_tmp_a va
        """,
        # step4
        f"""
            -- I49，step4，vb2 更新 vb 的 date1（最早的使用日）
            MERGE INTO vlt_tmp_b2 vb2
            USING vlt_tmp_b vb
            ON (vb2.setl_id = vb.setl_id AND vb2.item_j_code = vb.item_j_code)
            WHEN MATCHED THEN
                UPDATE SET vb2.date1 = vb.date1
        """,
        # step5
        f"""
            -- I49，step5，vb2 计算周期开始日，作为 date2（周期开始日）
            UPDATE vlt_tmp_b2 vb2
            SET vb2.date2 = vb2.date1 + FLOOR((vb2.item_day - vb2.date1) / {circle}) * {circle}
        """,
        # step6
        f"""
            -- I49，step6，vb2 根据 setl_id, item_j_code, date2 作为分类，统计 sum_q，插入 vb3，并且删除不违规的部分，即删除 NOT ( 数量 > numsLimit ) 的部分
            INSERT INTO vlt_tmp_b3 vb3 (setl_id, item_j_code, date2, sum_q)
            SELECT 
                vb2.setl_id, vb2.item_j_code, vb2.date2,
                SUM(vb2.q) AS sum_q
            FROM vlt_tmp_b2 vb2
            GROUP BY vb2.setl_id, vb2.item_j_code, vb2.date2
            HAVING SUM(vb2.q) > {numsLimit}
        """,
        # step7
        f"""
            -- I49，step7，vb2 删除不存在于 vb3 的 setl_id, item_j_code, date2 
            DELETE FROM vlt_tmp_b2 vb2
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b3 vb3
                WHERE vb2.setl_id = vb3.setl_id
                AND vb2.item_j_code = vb3.item_j_code
                AND vb2.date2 = vb3.date2
            )
        """,
        # step8
        f"""
            -- I49，step8，vb2 计算 accu_q，按 setl_id, item_j_code, date2 做分类，按 p 降序、d0_rowid 做升序，计算 accu_q
            UPDATE vlt_tmp_b2 vb2
            SET vb2.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY setl_id, item_j_code, date2
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_b2
                ) ranked
                WHERE ranked.d0_rowid = vb2.d0_rowid
            )
        """,
        # step9
        f"""
            -- I49，step9，vb2 计算 vlt_q，accu_q - numsLimit 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_b2 vb2
            SET vb2.vlt_q = CASE 
                WHEN vb2.accu_q - {numsLimit} < 0 THEN 0
                WHEN vb2.accu_q - {numsLimit} > vb2.q THEN vb2.q
                ELSE vb2.accu_q - {numsLimit}
            END
        """,
        # step10
        f"""
            -- I49，step10，vb2 更新 vb3 的 sum_q
            MERGE INTO vlt_tmp_b2 vb2
            USING vlt_tmp_b3 vb3
            ON (vb3.setl_id = vb2.setl_id AND vb3.item_j_code = vb2.item_j_code AND vb3.date2 = vb2.date2)
            WHEN MATCHED THEN
                UPDATE SET vb2.sum_q = vb3.sum_q
        """,
        # step11
        f"""
            -- I49，step11，vb2 更新 related，统计范围为 date1 ~ date1 + circle，时间范围内总数量是 sum_q
            UPDATE vlt_tmp_b2 vb2
            SET vb2.related = '统计范围： ' || TO_CHAR(vb2.date2, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(vb2.date2 + {circle} - 1, 'YYYY-MM-DD') || '，时间范围内总数量:' || vb2.sum_q
            WHERE vb2.date2 IS NOT NULL
        """,
        # step12
        f"""
            -- I49，step12，va 删除不存在于 vb2 的 d0_rowid 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b2 vb2
                WHERE va.d0_rowid = vb2.d0_rowid
            )
        """,
        # step13
        f"""
            -- I49，step13，va 根据 vb2 的 d0_rowid 更新 vb2 的 vlt_q, related
            MERGE INTO vlt_tmp_a va
            USING vlt_tmp_b2 vb2
            ON (va.d0_rowid = vb2.d0_rowid)
            WHEN MATCHED THEN
                UPDATE SET va.vlt_q = vb2.vlt_q, va.related = vb2.related
        """,
    ]

    return sqlList

def genM179(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 M179 的 SQL 语句
    非视网膜与糖尿病所致黄斑水肿，使用地塞米松玻璃体内植入剂，或者年度内超2支
    一年内累计数量 > value 的
    """


    # 从filterDatum中获取 value
    value = filterDatum['value']
    
    """
    ============ 思路 ============
    1. va 按 psn_no, j_year，统计 sum_q，提出不违规的部分，插入 vb
    2. va 删除不存在于 vb 的数据
    3. va 按 psn_no, j_year 分类，按 p 降序、d0_rowid 升序，计算 accu_q
    4. va 计算 vlt_q，accu_q - value 的部分，但是不能大于 q，也不能小于 0
    5. va 更新 vb 的 sum_q
    6. va 更新 related，年度内总数量是 sum_q

    ============ 参数 ============
    ● value：数量阈值    
    """

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        # step1
        f"""
            -- M179，step1，va 按 psn_no, j_year，统计 sum_q，提出不违规的部分，插入 vb
            INSERT INTO vlt_tmp_b vb (psn_no, j_year, sum_q)
            SELECT va.psn_no, va.j_year, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.psn_no, va.j_year
            HAVING SUM(va.q) > {value}
        """,
        # step2
        f"""
            -- M179，step2，va 删除不存在于 vb 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.psn_no = vb.psn_no
                AND va.j_year = vb.j_year
            )
        """,
        # step3
        f"""
            -- M179，step3，va 按 psn_no, j_year 分类，按 p 降序、d0_rowid 升序，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY psn_no, j_year
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step4
        f"""
            -- M179，step4，va 计算 vlt_q，accu_q - value 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE 
                WHEN va.accu_q - {value} < 0 THEN 0
                WHEN va.accu_q - {value} > va.q THEN va.q
                ELSE va.accu_q - {value}
            END
        """,
        # step5
        f"""
            -- M179，step5，va 更新 vb 的 sum_q
            MERGE INTO vlt_tmp_b vb
            USING vlt_tmp_a va
            ON (vb.psn_no = va.psn_no AND vb.j_year = va.j_year)
            WHEN MATCHED THEN
                UPDATE SET vb.sum_q = va.sum_q
        """,
        # step6
        f"""
            -- M179，step6，va 更新 related，年度内总数量是 sum_q
            UPDATE vlt_tmp_a va
            SET va.related = '年度内总数量:' || (
                SELECT vb.sum_q
                FROM vlt_tmp_b vb
                WHERE vb.psn_no = va.psn_no AND vb.j_year = va.j_year
            )
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE vb.psn_no = va.psn_no AND vb.j_year = va.j_year
            )
        """,
    ]

    return sqlList

def genM27(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 M27 的 SQL 语句
    除外三级医院的眼科，或者二级眼专科医院，同一患者同一医院，年度累计数量 > value 的部分
    """

    # 获得 hspList.json，然后找到 hsp_abbr 对应的医院信息，提取里面的 hsp_name
    with open('hspList.json', 'r', encoding='utf-8') as f:
        hsp_list = json.load(f)
    hsp_info = next((hsp for hsp in hsp_list if hsp['hsp_abbr'] == hsp_abbr), None)
    hsp_name = hsp_info['hsp_name'] if hsp_info else None
    hsp_lv = hsp_info['j_hsp_lv'] if hsp_info else None
    
    # 获得value
    value = filterDatum['value']

    """
    ============ 思路 ============
    1. 读取 hspList，如果医院等级是 2，且医院名字带有 %眼%，那么就整个 va 剔除；否则不做任何事情
    2. 读取 hspList，如果医院等级是 3，那么 va 就关联 m 剔除科室带眼、视光；
    3. va 按 psn_no, j_year，统计 sum_q，提出不违规的部分，插入 vb
    4. va 删除不存在于 vb 的数据
    5. va 按 psn_no, j_year 分类，按 p 降序、d0_rowid 升序，计算 accu_q
    6. va 计算 vlt_q，accu_q - value 的部分，但是不能大于 q，也不能小于 0
    7. va 更新 vb 的 sum_q
    8. va 更新 related，年度内总数量是 sum_q

    ============ 参数 ============
    ● value：数量阈值
    """

    # 如果医院的 hsp_lv是'2'且名字有"眼"，则不筛选，直接返回 空列表
    if hsp_lv in ('2', 2) and hsp_name and ('眼' in hsp_name or '视光' in hsp_name):
        return []
    
    sqlList = []
    # 如果医院的 hsp_lv是'3'，则剔除科室带眼、视光
    if hsp_lv in ('3', 3):
        sqlList.append(
            f"""
                -- M27，step2，读取 hspList，如果医院等级是 3，那么 va 就关联 m 剔除科室带眼、视光；
                DELETE FROM vlt_tmp_a va
                WHERE EXISTS (
                    SELECT 1 FROM m
                    WHERE va.setl_id = m.setl_id
                    AND (m.in_dept LIKE '%眼%' OR m.in_dept LIKE '%视光%' OR m.out_dept LIKE '%眼%' OR m.out_dept LIKE '%视光%')
                ) OR va.item_dept LIKE '%眼%' OR va.item_dept LIKE '%视光%' OR va.apply_dept LIKE '%眼%' OR va.apply_dept LIKE '%视光%'
            """
        )
    
    sqlList += [
        # step3
        f"""
            -- M27，step3，va 按 psn_no, j_year，统计 sum_q，提出不违规的部分，插入 vb
            INSERT INTO vlt_tmp_b vb (psn_no, j_year, sum_q)
            SELECT va.psn_no, va.j_year, SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
            GROUP BY va.psn_no, va.j_year
            HAVING SUM(va.q) > {value}
        """,
        # step4
        f"""
            -- M27，step4，va 删除不存在于 vb 的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE va.psn_no = vb.psn_no
                AND va.j_year = vb.j_year
            )
        """,
        # step5
        f"""
            -- M27，step5，va 按 psn_no, j_year 分类，按 p 降序、d0_rowid 升序，计算 accu_q
            UPDATE vlt_tmp_a va
            SET va.accu_q = (
                SELECT cumulative_q 
                FROM (
                    SELECT d0_rowid,
                        SUM(q) OVER (
                            PARTITION BY psn_no, j_year
                            ORDER BY p DESC, d0_rowid ASC
                            ROWS UNBOUNDED PRECEDING
                        ) AS cumulative_q
                    FROM vlt_tmp_a
                ) ranked
                WHERE ranked.d0_rowid = va.d0_rowid
            )
        """,
        # step6
        f"""
            -- M27，step6，va 计算 vlt_q，accu_q - value 的部分，但是不能大于 q，也不能小于 0
            UPDATE vlt_tmp_a va
            SET va.vlt_q = CASE
                WHEN va.accu_q - {value} < 0 THEN 0
                WHEN va.accu_q - {value} > va.q THEN va.q
                ELSE va.accu_q - {value}
            END
        """,
        # step7
        f"""
            -- M27，step7，va 更新 vb 的 sum_q
            MERGE INTO vlt_tmp_b vb
            USING vlt_tmp_a va
            ON (vb.psn_no = va.psn_no AND vb.j_year = va.j_year)
            WHEN MATCHED THEN
                UPDATE SET vb.sum_q = va.sum_q
        """,
        # step8
        f"""
            -- M27，step8，va 更新 related，年度内总数量是 sum_q
            UPDATE vlt_tmp_a va
            SET va.related = '年度内总数量:' || (
                SELECT vb.sum_q
                FROM vlt_tmp_b vb
                WHERE vb.psn_no = va.psn_no AND vb.j_year = va.j_year
            )
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_b vb
                WHERE vb.psn_no = va.psn_no AND vb.j_year = va.j_year
            )
        """,
    ]

    return sqlList

def genC21(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    """
    生成规则 C21 的 SQL 语句
    针对“同一家医院，所有有C的单据号下，B的数量总和>A的数量总和 * value 的，
    整个医院达到这个条件，就违规
    """
    # 从 filterDatum 中获取 valueAApply, valueBApply, valueCApply
    valueAApply = filterDatum['value_a_apply']
    valueBApply = filterDatum['value_b_apply']
    valueCApply = filterDatum['value_c_apply']
    # 分别将这三个值转换为 SQL 文字
    valueACodesSql, valueANamesSql = handleCustomApply(valueAApply, filterDatum['value_a'], type='item', tblAbbr='d')
    valueBCodesSql, valueBNamesSql = handleCustomApply(valueBApply, filterDatum['value_b'], type='item', tblAbbr='d')
    valueCCodesSql, valueCNamesSql = handleCustomApply(valueCApply, filterDatum['value_c'], type='item', tblAbbr='d')
    # 从filterDatum 中 获取 value
    value = filterDatum['value']

    # 假如 valueACodesSql 是空的，则不进行筛选，删除所有 va 表中的数据
    if not valueACodesSql and not valueANamesSql:
        return [
            f"""
                -- C21，valueAApply 为空，直接删除 va 表中的所有数据
                DELETE FROM vlt_tmp_a
            """
        ]
    
    
    
    """
    ============ 思路 ============
    vb表用于统计 va 表中的 sum_q（即B类项目），vb2表用于统计A类项目
    
    1. va 表关联 d 表，删除结算号下没有使用 C 的 setl_id
    2. vb 表统计 va 表中的 sum_q
    3. vb2 表统计 va 表，关联 d 表，va 出现的 setl_id 的 A 的 sum_q
    4. 如果 NOT vb.sum_q/vb2.sum_q>value，那么 va 全部删除
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        # step1
        f"""
            -- C21，step1，va 表关联 d 表，删除结算号下没有使用 C 的 setl_id
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1 FROM d d
                WHERE va.setl_id = d.setl_id
                {valueCCodesSql} {valueCNamesSql}
            )
        """,
        # step2
        f"""
            -- C21，step2，vb 表统计 va 表中的 sum_q
            INSERT INTO vlt_tmp_b vb (sum_q)
            SELECT SUM(va.q) AS sum_q
            FROM vlt_tmp_a va
        """,
        # step3
        f"""
            -- C21，step3，vb2 表统计 va 表，关联 d 表，va 出现的 setl_id 的 A 的 sum_q
            INSERT INTO vlt_tmp_b2 vb2 (sum_q)
            SELECT SUM(d.q) AS sum_q
            FROM d
            WHERE EXISTS (
                SELECT 1 FROM vlt_tmp_a va
                WHERE va.setl_id = d.setl_id
            ) {valueACodesSql} {valueANamesSql}
        """,
        # step4
        f"""
            -- C21，step4，如果 NOT vb.sum_q/vb2.sum_q>value，那么 va 全部删除
            DELETE FROM vlt_tmp_a va
            WHERE NOT (
                (SELECT vb.sum_q FROM vlt_tmp_b vb) / NULLIF((SELECT vb2.sum_q FROM vlt_tmp_b2 vb2), 0) > {value}
            ) 
            OR (SELECT vb2.sum_q FROM vlt_tmp_b2 vb2) IS NULL
        """,
    ]
    
    return sqlList
